﻿// ============================================================
//
// 创建时间: 2011-8-11
// ============================================================
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;

using DQS.EL;

namespace DQS.DAL
{
	public static class DQ_USERSService
	{
		public static int AddDQ_USERS(DQ_USERS _DQ_USERS)
		{
			int res;
            string sqlStr = "insert into DQ_USERS (USER_LOGIN,USER_PASS,REAL_NAME,f_orga_id,USER_STATUS,IS_ADMIN)"
                            + " values(:USER_LOGIN,:USER_PASS,:REAL_NAME,:f_orga_id,:USER_STATUS,:IS_ADMIN)";

            OracleParameter[] _param = {
			new OracleParameter(":USER_LOGIN",OracleType.VarChar),
			new OracleParameter(":USER_PASS",OracleType.VarChar),
			new OracleParameter(":REAL_NAME",OracleType.VarChar),
			new OracleParameter(":f_orga_id",OracleType.Number,10),
			new OracleParameter(":USER_STATUS",OracleType.Char,1),
			new OracleParameter(":IS_ADMIN",OracleType.VarChar)
			};
			
			_param[0].Value = _DQ_USERS.USER_LOGIN;
			_param[1].Value = _DQ_USERS.USER_PASS;
			_param[2].Value = _DQ_USERS.REAL_NAME;
			_param[3].Value = _DQ_USERS.F_Orga_ID.Orga_ID;
			_param[4].Value = _DQ_USERS.USER_STATUS;
			_param[5].Value = _DQ_USERS.IS_ADMIN;

            res = OracleHelper.ExecuteNonQuery(sqlStr, CommandType.Text, _param);
			return res;
		}

        public static DQ_USERS ValidUser(DQ_USERS _DQ_User)
        {
            DQ_USERS _obj = null;
            string sqlStr = "SELECT REAL_NAME,f_orga_id,IS_ADMIN,USER_ID FROM DQ_USERS " +
                             "WHERE UPPER(USER_LOGIN)=UPPER(:USER_LOGIN) AND USER_PASS=:USER_PASS " + 
                               "AND USER_STATUS='Y'";
            OracleParameter[] _param ={
			    new OracleParameter(":USER_LOGIN",OracleType.VarChar),
                new OracleParameter(":USER_PASS",OracleType.VarChar)
			};
            _param[0].Value = _DQ_User.USER_LOGIN;
            _param[1].Value = _DQ_User.USER_PASS;
            using (OracleDataReader odr = OracleHelper.ExecuteReader(sqlStr, CommandType.Text, _param))
            {
                if (odr.Read())
                {
                    _obj = new DQ_USERS();
                    _obj.USER_LOGIN = _DQ_User.USER_LOGIN;
                    _obj.REAL_NAME = odr.GetString(0);
                    DQ_Organization orga = new DQ_Organization();
                    orga.Orga_ID = odr.GetInt32(1);
                    _obj.F_Orga_ID = orga;
                    _obj.IS_ADMIN = odr.GetString(2);
                    _obj.USER_ID = odr.GetDecimal(3);
                }
            }
            return _obj;
        }

        public static DQ_USERS ValidUserByLogin(DQ_USERS _DQ_User)
        {
            DQ_USERS _obj = null;
            string sqlStr = "SELECT REAL_NAME,f_orga_id,IS_ADMIN,USER_ID FROM DQ_USERS " +
                             "WHERE UPPER(USER_LOGIN)=UPPER(:USER_LOGIN)";
            OracleParameter[] _param ={
			    new OracleParameter(":USER_LOGIN",OracleType.VarChar,20)
			};
            _param[0].Value = _DQ_User.USER_LOGIN;
            using (OracleDataReader odr = OracleHelper.ExecuteReader(sqlStr, CommandType.Text, _param))
            {
                if (odr.Read())
                {
                    _obj = new DQ_USERS();
                    _obj.USER_LOGIN = _DQ_User.USER_LOGIN;
                    _obj.REAL_NAME = odr.GetString(0);
                    DQ_Organization orga = new DQ_Organization();
                    orga.Orga_ID = odr.GetInt32(1);
                    _obj.F_Orga_ID = orga;
                    _obj.IS_ADMIN = odr.GetString(2);
                    _obj.USER_ID = odr.GetDecimal(3);
                }
            }
            return _obj;
        }

        public static IList<DQ_USERS> SelectUsersByLogin(string argLogin)
        {
            IList<DQ_USERS> _objs = new List<DQ_USERS>();
            string sqlStr = "SELECT USER_LOGIN,REAL_NAME,t2.orga_name AS Organization,t4.data_name AS IS_ADMIN,USER_ID,t3.data_name AS USER_STATUS FROM DQ_USERS t1" +
                             " LEFT JOIN  DQ_Organizations t2 ON t1.f_orga_id = t2.orga_id " +
                             " LEFT JOIN  Dq_pubdata t3 ON t1.USER_STATUS = t3.data_code AND t3.data_type='UserAble' " +
                             " LEFT JOIN  Dq_pubdata t4 ON t1.IS_ADMIN = t4.data_code AND t4.data_type='GelBool' " + 
                             " WHERE t1.USER_LOGIN LIKE :USER_LOGIN" +
                             " ORDER BY t2.orga_id,USER_STATUS DESC";
            OracleParameter[] _params = {
                    new OracleParameter(":USER_LOGIN",OracleType.VarChar,20)
            };         
            _params[0].Value = argLogin+"%";
            using (OracleDataReader odr = OracleHelper.ExecuteReader(sqlStr, CommandType.Text, _params))
            {
                while (odr.Read())
                {
                    DQ_USERS _obj = new DQ_USERS();
                    _obj.USER_LOGIN = odr.GetString(0);
                    _obj.REAL_NAME = odr.GetString(1);
                    DQ_Organization orga = new DQ_Organization();
                    orga.Orga_Name = odr.GetString(2);
                    _obj.F_Orga_ID = orga;
                    _obj.IS_ADMIN = odr.GetString(3);
                    _obj.USER_ID = odr.GetDecimal(4);
                    _obj.USER_STATUS = odr.GetString(5);
                    _objs.Add(_obj);
                }
            }
            return _objs;
        }

        public static IList<DQ_USERS> SelectUsers()
        {
            IList<DQ_USERS> _objs = new List<DQ_USERS>();
            string sqlStr = "SELECT USER_LOGIN,REAL_NAME,t2.orga_name AS Organization,t4.data_name AS IS_ADMIN,USER_ID,t3.data_name AS USER_STATUS FROM DQ_USERS t1" +
                             " LEFT JOIN  DQ_Organizations t2 ON t1.f_orga_id = t2.orga_id " +
                             " LEFT JOIN  Dq_pubdata t3 ON t1.USER_STATUS = t3.data_code AND t3.data_type='UserAble' " + 
                             " LEFT JOIN  Dq_pubdata t4 ON t1.IS_ADMIN = t4.data_code AND t4.data_type='GelBool' " +
                             " ORDER BY t2.orga_id,USER_STATUS DESC";

            using (OracleDataReader odr = OracleHelper.ExecuteReader(sqlStr, CommandType.Text, null))
            {
                while (odr.Read())
                {
                    DQ_USERS _obj = new DQ_USERS();
                    _obj.USER_LOGIN = odr.GetString(0);
                    _obj.REAL_NAME = odr.GetString(1);
                    DQ_Organization orga = new DQ_Organization();
                    orga.Orga_Name = odr.GetString(2);
                    _obj.F_Orga_ID = orga;
                    _obj.IS_ADMIN = odr.GetString(3);
                    _obj.USER_ID = odr.GetDecimal(4);
                    _obj.USER_STATUS = odr.GetString(5);
                    _objs.Add(_obj);
                }
            }
            return _objs;
        }

        public static int ModifyStatus(decimal argUserID)
        {
            string sqlStr = "UPDATE DQ_USERS SET USER_STATUS=DECODE(USER_STATUS,'Y','N','Y') WHERE USER_ID =:user_id";
            OracleParameter[] _params = {
                new OracleParameter("user_id",OracleType.Number)
            };
            _params[0].Value = argUserID;
            return OracleHelper.ExecuteNonQuery(sqlStr, CommandType.Text, _params);
        }

        public static bool IsOnlyAdmin(decimal argUserID)
        {
            int count = -1;
            string sqlStr = "SELECT count(1) FROM dq_users WHERE user_status = 'Y' AND is_admin = 'Y' AND user_id <> :user_id";
            OracleParameter[] _params = {
                    new OracleParameter(":user_id",OracleType.Number)
            };
            _params[0].Value = argUserID;
            count = Int32.Parse(OracleHelper.ExecuteScalar(sqlStr, CommandType.Text, _params).ToString());
            return (count == 0);
        }

        public static int ChangePassword(DQ_USERS argUser)
        {
            int effRow = 0;
            string sqlStr = "UPDATE DQ_USERS SET USER_PASS =:USER_PASS WHERE USER_ID =:USER_ID";
            OracleParameter[] _params = {
                new OracleParameter(":USER_PASS",OracleType.VarChar,200),
                new OracleParameter(":USER_ID",OracleType.Number)
            };
            _params[0].Value = argUser.USER_PASS;
            _params[1].Value = argUser.USER_ID;
            effRow = OracleHelper.ExecuteNonQuery(sqlStr, CommandType.Text, _params);
            return effRow;
        }
	}
}

